package sqlx

import (
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"github.com/jmoiron/sqlx"
)

var db *sqlx.DB

type user struct {
	ID   int
	Name string
	Age  int
}

func checkError(err error) {
	if err != nil {
		panic(err)
	}
}

func InitDb() *sqlx.DB {
	dsn := "root:453924241@tcp(localhost:3306)/test?charset=utf8mb4"

	db, err := sqlx.Connect("mysql", dsn)
	checkError(err)

	db.SetMaxIdleConns(0)
	db.SetMaxOpenConns(20)
	return db
}

func QueryRow(db *sqlx.DB) {
	sql := "select id, name, age from user where id = ?"
	var u user
	err := db.Get(&u, sql, 1)
	checkError(err)

	fmt.Println(u)
}

func QueryRows(db *sqlx.DB) {
	sql := "select * from user where id > ?"
	var users []user

	err := db.Select(&users, sql, 1)
	checkError(err)
	fmt.Println(users)
}

func Insert(db *sqlx.DB) {
	sql := "insert into user(name, age) values (?,?)"
	ret, err := db.Exec(sql, "我叫林芳伊", 18)
	checkError(err)
	fmt.Println(ret.LastInsertId())
}

func Update(db *sqlx.DB) {
	sql := "update user set age = 7 where id = ?"
	ret, err := db.Exec(sql, 6)
	checkError(err)
	fmt.Println(ret.RowsAffected())
}

func Delete(db *sqlx.DB) {
	sql := "delete from user where id = ?"
	ret, err := db.Exec(sql, 6)
	checkError(err)
	fmt.Println(ret.RowsAffected())
}

func NamedExec(db *sqlx.DB) {
	sql := "insert into user(name, age) values (:name, :age)"
	ret, err := db.NamedExec(sql, map[string]interface{}{
		"name": "范帅",
		"age":  20,
	})
	checkError(err)
	fmt.Println(ret.LastInsertId())
}

func NamedQuery(db *sqlx.DB) {
	sql := "select * from user where id > :id"
	rows, err := db.NamedQuery(sql, map[string]interface{}{
		"id": 1,
	})

	checkError(err)
	defer rows.Close()
	var users []user

	for rows.Next() {
		var u user
		rows.StructScan(&u)
		users = append(users, u)
	}

	fmt.Println(users)
}

func TransactionDemo(db *sqlx.DB) (err error) {
	tx, err := db.Beginx()
	checkError(err)

	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p)
		} else if err != nil {
			fmt.Println("rollback")
			tx.Rollback()
		} else {
			err = tx.Commit()
			fmt.Println("commit")
		}
	}()

	sql1 := "update user set age = 20 where id = ?"
	rs, err := tx.Exec(sql1, 2)
	fmt.Println(rs.RowsAffected())

	sql2 := "update user set age = 50 where id = ?"
	rs, err = tx.Exec(sql2, 4)
	fmt.Println(rs.RowsAffected())

	return err
}
